package com.sunyard.utils;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.springframework.web.multipart.MultipartFile;

import java.util.List;

/**
 * excel工具类
 *
 * @author guangw.dang
 * @Date: 2022/5/11 15:06
 */
@Slf4j
public class ExcelUtil {

    public static final String FILE_TYPE_XLS = "xls";

    /**
     * 校验excel格式
     *
     * @param file [上传的文件]
     * @author: guangw.dang
     * @Date: 2022/3/4 10:57
     **/
    public static void inspecExcel(MultipartFile file) throws Exception {
        //判断文件版本
        String fileName = file.getOriginalFilename();
        if (fileName == null || "".equals(fileName)) {
            log.info("文件名不能为空");
            throw new Exception("文件名不能为空");
        }

        String fileType = fileName.substring(fileName.lastIndexOf(".") + 1);
        if (!(FILE_TYPE_XLS.equalsIgnoreCase(fileType))) {
            throw new Exception("上传文件只能是xls格式!");
        }
        log.info("上传的文件大小：" + file.getSize());
        int fileSize = 1024;
        if (file.getSize() > fileSize * fileSize) {
            log.info("上传文件不能大于1MB");
            throw new Exception("上传文件不能大于1MB");
        }
    }

    /**
     * 判断文件标特是否正确
     *
     * @param list  list
     * @param sheet [list, sheet]
     * @author: guangw.dang
     * @Date: 2022/3/8 9:40
     **/
    public static void excuteTittle(List<String> list, Sheet sheet) throws Exception {
        Row tittle = sheet.getRow(1);
        for (int index = 0; index < list.size(); index++) {
            if (tittle.getCell(index) != null) {
                validateIsText(tittle.getCell(index));
            }
            if (!list.get(index).equals(tittle.getCell(index).getStringCellValue())) {
                log.info("错误的字段标题：{}", list.get(index));
                throw new Exception("上传的文件模板不正确");
            }
        }
    }

    private static void validateIsText(Cell cell) throws Exception {
        if (cell == null) {
            throw new Exception("单元格为null");
        }
        //判断单元格类型是否为文本类型
        if (1 != cell.getCellType()) {
            throw new Exception("单元格类型不是文本类型");
        }
    }

    /**
     * 校验单元格类型不是文本类型
     *
     * @param list  list
     * @param sheet sheet
     * @param line  [list, sheet, line]
     * @return org.apache.poi.ss.usermodel.Row
     * @author: guangw.dang
     * @Date: 2022/3/8 9:16
     **/
    public static Row excuteCell(List<String> list, Sheet sheet, int line) throws Exception {
        Row row = sheet.getRow(line);
        if (null != row) {
            for (int index = 0; index < list.size(); index++) {
                validateIsText(row.getCell(index));
            }
        }
        return row;
    }

}
